# -*- coding: utf-8 -*-
# @Time: 2022/8/21 4:15
# @Author: foxhuty
# @File: to_sql.py
# @Software: PyCharm
# @Based on: python 3.10.6
import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime


# df2 = pd.read_sql(sql="select * from db_todo.my_schedule", con=engine, index_col='id')
# df = pd.read_excel(
#     r'D:\年级管理数据\高2021级\高二上\高二上半期考试成绩分析\高二上半期考试成绩分析图表制作\高2021级半期考试成绩数据.xlsx',
#     sheet_name='理科')
# print(df.head())
# # print(df.iterrows())
# for idx, row in df.iterrows():
#     name = row['姓名']
#     scores = row['总分']
#     print(name, scores)
# print(df.size)
# print(df.count())
# #
# lst = df.count()
# # print(lst)
# lst_df = list(zip(lst.index.tolist(), lst.tolist()))
#
# print(lst_df)
#
# print(datetime.now())
def read_into_sql(db_name, file_name, sheet_name, tb_name):
    engine = create_engine(f"mysql+pymysql://root:foxmmer@localhost/{db_name}")
    conn=engine.connect()
    data = pd.read_excel(file_name, sheet_name=sheet_name)
    data.to_sql(name=tb_name, con=conn, index=False, if_exists='replace')
    conn.close()


def read_from_mysql(db_name, tb_name):
    engine = create_engine(f'mysql+pymysql://root:foxmmer@localhost/{db_name}')
    conn=engine.connect()
    data = pd.read_sql(sql=f"select * from {tb_name}", con=conn)
    print(data)
    with pd.ExcelWriter(r'D:\成绩统计结果\staff.xlsx') as writer:
        data.to_excel(writer, sheet_name='台帐')
    conn.close()


def get_weekday():
    week = datetime.weekday(datetime.now())
    week_name = ['星期一', '星期二', '星期三', '星期四', '星期五', '星期六', '星期日']
    week_day = week_name[week]
    return week_day


if __name__ == '__main__':
    file = r'D:\成绩统计结果\school_total.xlsx'

    read_into_sql('excel_sql',file, 'Sheet1', 'school_staff')
    read_from_mysql('excel_sql', 'school_staff')

